/**
 * @author btan
 * @email: btan@tma.com.vn - anbtan@gmail.com
 */
package com.vital.service;


import com.vital.dao.Category;
import com.vital.dao.Location;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseService {
	//---mShopping Database---
	static final String DATABASE_NAME = "mshopdb.sqlite";
	static final int DATABASE_VERSION = 7;
	
	//---Table Name---
	static final String TABLE_CATEGORIES = "CATEGORIES";
	static final String TABLE_LOCATIONS = "LOCATIONS";
	static final String TABLE_BOOKMARKS = "BOOKMARKS";
	static final String TABLE_HISTORIES = "HISTORIES";
	
	//---Table Field CATEGORIES---
	static final String CATE_ID = "_id";
	static final String CATE_NAME = "CateName";
	static final String CATE_ICON = "CateIcon";
	static final String CATE_READONLY = "CateReadOnly";
	
	//---Table Field LOCATIONS---
	static final String LOCA_ID = "_id";
	static final String LOCA_LAT = "Lat";
	static final String LOCA_LONG = "Lng";
	static final String LOCA_NAME = "Name";
	static final String LOCA_PHONE = "Phone";
	static final String LOCA_ADDRESS = "Address";
	static final String LOCA_CATEGORY = "Category";
	static final String LOCA_ACCURACY = "Accuracy";
	
	//---Table Field BOOKMARKS---
	static final String BOOK_ID = "_id";
	static final String BOOK_LOCATION_ID = "LocationId";
	
	//---Table Field HISTORIES---
	static final String HIS_ID = "_id";
	static final String HIS_LOCATION_ID = "LocationId";
	static final String HIS_CREATED_DATE = "Date";
	
	//---Create Table CATEGORIES String---
	static final String CREATE_TABLE_CATEGORIES = "" + 
		"CREATE TABLE " + TABLE_CATEGORIES + " (" + CATE_ID + " INTEGER PRIMARY KEY  NOT NULL , " + 
		CATE_NAME + " VARCHAR, " + CATE_ICON + " VARCHAR, " + CATE_READONLY +" INTEGER DEFAULT 1)";
	
	//---Create Table LOCATIONS String---
	static final String CREATE_TABLE_LOCATIONS = "" +
		"CREATE TABLE " + TABLE_LOCATIONS + " (" + LOCA_ID + " INTEGER PRIMARY KEY NOT NULL , " + LOCA_LAT + 
		" FLOAT, " + LOCA_LONG + " FLOAT, " + LOCA_NAME + " VARCHAR, " + 
		LOCA_PHONE + " VARCHAR, " + LOCA_ADDRESS + " VARCHAR, " + LOCA_CATEGORY +
		" INTEGER, "+ LOCA_ACCURACY + " INTEGER)";
	
	//---Create Table HISTORIES String---
	static final String CREATE_TABLE_HISTORIES = "" +
		"CREATE TABLE " + TABLE_HISTORIES + " (" + HIS_ID + " INTEGER PRIMARY KEY  NOT NULL , " +
			HIS_LOCATION_ID + " INTEGER, " + HIS_CREATED_DATE + " DATETIME)";
	
	//---Create Table BOOKMARKS String---
	static final String CREATE_TABLE_BOOKMARKS = "" +
		"CREATE TABLE " + TABLE_BOOKMARKS + " (" + BOOK_ID + " INTEGER PRIMARY KEY  NOT NULL , " +
			BOOK_LOCATION_ID + " INTEGER)";
	
	//---Member variable---
	final Context mContext;
	DatabaseHelper mDbHelper;
	SQLiteDatabase db;
	static final String TAG = "DatabaseService";
	
	//---Get all categories---
	String sqlGetAllCategories = "Select * From " + TABLE_CATEGORIES;
	
	//---Get all bookmarks---
	String sqlGetAllBookmarks = "Select A."+LOCA_ID+","+LOCA_LAT+","+LOCA_LONG+","+LOCA_NAME+","+LOCA_PHONE+
									","+LOCA_ADDRESS+","+LOCA_CATEGORY+",B."+BOOK_ID+","+LOCA_ACCURACY+" "+
								"From "+ TABLE_LOCATIONS + " A " + "INNER JOIN " + TABLE_BOOKMARKS + " B" +
									" ON A."+LOCA_ID+"=B."+BOOK_LOCATION_ID+"";
	
	//---Get all histories---
	String sqlGetAllHistories = "Select A."+LOCA_ID+","+LOCA_LAT+","+LOCA_LONG+","+LOCA_NAME+","+LOCA_PHONE+
									","+LOCA_ADDRESS+","+LOCA_CATEGORY+",B."+HIS_ID+","+HIS_CREATED_DATE+","+LOCA_ACCURACY+" "+
								"From "+ TABLE_LOCATIONS + " A " + "INNER JOIN " + TABLE_HISTORIES + " B" +
									" ON A."+LOCA_ID+"=B."+HIS_LOCATION_ID+"";
			
	//---Database Constructor---
	public DatabaseService(Context ctx) {
		this.mContext = ctx;
		mDbHelper = new DatabaseHelper(this.mContext);
	}

	//---Database Helper Class---
	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			try {
				db.execSQL(CREATE_TABLE_CATEGORIES);
				db.execSQL(CREATE_TABLE_LOCATIONS);
				db.execSQL(CREATE_TABLE_BOOKMARKS);
				db.execSQL(CREATE_TABLE_HISTORIES);
				
				//---Insert five default category---
				insertDefaultCategory(db, "Restaurant", "restaurant");
				insertDefaultCategory(db, "Entertainment", "entertainment");
				insertDefaultCategory(db, "ATM", "atm");
				insertDefaultCategory(db, "Shopping", "shopping");
				insertDefaultCategory(db, "Travel", "travel");
				
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + 
					", which will destroy all old data");
			//---Drop Table---
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKMARKS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISTORIES);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATIONS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
			//---Recreate Database---
			onCreate(db);
		}
		
		//---Insert Category to DB---
		private long insertDefaultCategory(SQLiteDatabase db, String cateName, String cateIcon) {
			ContentValues vlContent = new ContentValues();
			vlContent.put(CATE_NAME, cateName);
			vlContent.put(CATE_ICON, cateIcon);
			vlContent.put(CATE_READONLY, 1);
			return db.insert(TABLE_CATEGORIES, null, vlContent);
		}
	}
	
	//---opens the database---
	public DatabaseService open() throws SQLException{
		db = mDbHelper.getWritableDatabase();
		return this;
	}
	
	//---closes the database---
	public void close() {
		mDbHelper.close();
	}
	
	//---insert category to database---
	public long insertCategory(Category category) {
		ContentValues vlContent = new ContentValues();
		vlContent.put(CATE_NAME, category.getCateName());
		vlContent.put(CATE_ICON, category.getCateIcon());
		vlContent.put(CATE_READONLY, category.getCateReadOnly());
		return db.insert(TABLE_CATEGORIES, null, vlContent);
	}
		
	//---Get all Categories from Database---
	public Cursor getAllCategories() {
		return db.rawQuery(this.sqlGetAllCategories, null);
	}
	
	//---Delete a category---
	public boolean deleteCategory(long cateId) {
		return db.delete(TABLE_CATEGORIES, CATE_ID + "=" + cateId, null) > 0;
	}
	
	//---Update a category---
	public boolean updateCategory(Category category) {
		ContentValues vlContent = new ContentValues();
		vlContent.put(CATE_NAME, category.getCateName());
		vlContent.put(CATE_ICON, category.getCateIcon());
		return db.update(TABLE_CATEGORIES, vlContent, CATE_ID + "=" + category.getCateId(), null) > 0;
	}
	
	//---insert Location---
	public long insertLocation(Location location) {
		ContentValues vlContent = new ContentValues();
		vlContent.put(LOCA_LAT, (float)location.getLat());
		vlContent.put(LOCA_LONG, (float)location.getLng());
		vlContent.put(LOCA_NAME, location.getName());
		vlContent.put(LOCA_PHONE, location.getPhone());
		vlContent.put(LOCA_ADDRESS, location.getAddress());
		vlContent.put(LOCA_CATEGORY, (int)location.getCategory());
		vlContent.put(LOCA_ACCURACY, location.getAccuracy());
		return db.insert(TABLE_LOCATIONS, null, vlContent);
	}
	
	//---insert bookmark---
	public long insertBookMark(int locationId) {
		ContentValues vlContent = new ContentValues();
		vlContent.put(BOOK_LOCATION_ID, locationId);
		return db.insert(TABLE_BOOKMARKS, null, vlContent);
	}
	
	//---remove bookmark---
	public boolean removeBookmark(int bookmarkId) {
		return db.delete(TABLE_BOOKMARKS, BOOK_ID + "=" + bookmarkId, null) > 0;
	}
	
	//---get all bookmark location---
	public Cursor getAllBookmarks() {
		return db.rawQuery(this.sqlGetAllBookmarks, null);
	}
	
	//---insert Histories---
	public long insertHistory(int locationId) {
		ContentValues vlContent = new ContentValues();
		vlContent.put(HIS_LOCATION_ID, locationId);
		vlContent.put(HIS_CREATED_DATE, "(DATETIME('NOW'))");
		return db.insert(TABLE_HISTORIES, null, vlContent);
	}
	
	//---get all histories location---
	public Cursor getAllHistories() {
		return db.rawQuery(sqlGetAllHistories, null);
	}
	
	//---remove all histories---
	public boolean removeAllHistories() {
		return db.delete(TABLE_HISTORIES, "", null)>0;
	}
	
	//---remove a specify history base on history id---
	public boolean removeHistory(int historyId) {
		return db.delete(TABLE_HISTORIES, HIS_ID + "=" + historyId, null) > 0;
	}
	
	//---delete all history ---
	public boolean deleteAllBookmark() {
		return db.delete(TABLE_BOOKMARKS, "", null) > 0;
	}
	
	//---remove history base on created date---
//	public boolean removeHistoryBaseSchedule(int schedule) {
//		return db.delete(TABLE_HISTORIES, HIS_CREATED_DATE + ">=" + now - schedule, null) > 0;
//	}
}
